BEGIN;

DROP VIEW win_observation_statistics_view;

CREATE OR REPLACE VIEW win_observation_statistics_view AS 
SELECT ( SELECT count(drupal_users.uid) AS count
           FROM drupal_users
          WHERE drupal_users.uid > 1) AS num_contributors, 
        ( SELECT count(DISTINCT drupal_profile_values.value) AS count
           FROM drupal_profile_values
          WHERE drupal_profile_values.fid::integer = (( SELECT drupal_profile_fields.fid
                   FROM drupal_profile_fields
                  WHERE drupal_profile_fields.name::text = 'profile_country'::text))) AS num_contributor_countries, 
        ( SELECT count(win_observations.id) AS count
           FROM win_observations) AS num_contributions, 
        ( SELECT count(DISTINCT win_validation.observation_id) AS count
           FROM win_validation) AS num_validated_contributions,
        (select count(uid) from win_validators where status = 1) as num_experts,
        ( SELECT count(DISTINCT drupal_profile_values.value) AS count
           FROM drupal_profile_values
          WHERE drupal_profile_values.fid::integer = ( SELECT drupal_profile_fields.fid
                   FROM drupal_profile_fields
                  WHERE drupal_profile_fields.name::text = 'profile_country'::text)
          AND drupal_profile_values.uid in (select uid from win_validators where status = 1)
                  ) AS num_expert_countries;


COMMIT;